package org.alex.samples.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

//import org.alex.samples.dao.ExamSubjectDAOImpl.UserMapper;
import org.alex.samples.model.ExamSubject;
import org.hibernate.type.StringType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;



@Repository
public class ExamSubjectDAOImpl extends BaseHibernateDAO<ExamSubject> implements IExamSubjectDAO {
	@Autowired
	private JdbcTemplate jdbcTemplate;
		
	public ExamSubjectDAOImpl() {
		super();
	}

	
	private static final class UserMapper implements RowMapper<ExamSubject>{
		public ExamSubject mapRow(ResultSet rs,int rowNum) throws SQLException{
			ExamSubject examSubject=new ExamSubject();
			examSubject.setSubId(rs.getInt("sub_id"));
			examSubject.setName("name");
			return examSubject;
		}
	}
	
	public List<ExamSubject> findAllExamSubject4Mapper(){
		String sql="select * from exam_subject";
		List<ExamSubject> userList=jdbcTemplate.query(sql,new UserMapper());
		return userList;
	}
	
	
	//查询全部数据表功能方法	
	public List<ExamSubject> findAllExamSubject() {
		String sql="select * from exam_subject";
		List<ExamSubject> examSubjectList=jdbcTemplate.query(sql, new RowMapper<ExamSubject>(){
			public ExamSubject mapRow(ResultSet rs,int num) throws SQLException{
				ExamSubject examSubject=new ExamSubject();
				examSubject.setSubId(rs.getInt("sub_id"));
				examSubject.setName(rs.getString("name"));
				examSubject.setDescription(rs.getString("description"));
				examSubject.setTestCount(rs.getInt("test_count"));
				examSubject.setTotalTime(rs.getInt("total_time"));
				examSubject.setTotalScore(rs.getDouble("total_score"));
				examSubject.setStatus(rs.getInt("status"));
				
				return examSubject;
			}
		});
				return examSubjectList;
	}

	
	//查询一条数据表功能方法
	public ExamSubject getExamSubjectById(Integer subId) {
		String sql="select * from exam_subject where sub_id=?";
		return jdbcTemplate.queryForObject(sql, new Object[]{subId},new RowMapper<ExamSubject>(){
			public ExamSubject mapRow(ResultSet rs,int num) throws SQLException{
				ExamSubject examSubject=new ExamSubject();
				examSubject.setSubId(rs.getInt("sub_id"));
				examSubject.setName(rs.getString("name"));
				examSubject.setDescription(rs.getString("description"));
				examSubject.setTestCount(rs.getInt("test_count"));
				examSubject.setTotalTime(rs.getInt("total_time"));
				examSubject.setTotalScore(rs.getDouble("total_score"));
				examSubject.setStatus(rs.getInt("status"));
				
				return examSubject;
			}
		});
	}

	//新增方法
	public Integer createExamSubject(ExamSubject examSubject) throws Exception {
		String sql="insert into exam_subject values(?,?,?,?,?,?,?)";
		return jdbcTemplate.update(sql,new Object[]{examSubject.getSubId(),examSubject.getName()
				,examSubject.getDescription(),examSubject.getTestCount(),examSubject.getTotalTime()
				,examSubject.getTotalScore(),examSubject.getStatus()});
		
	}

	
	//修改方法
	public Integer editExamSubject(ExamSubject examSubject) throws Exception {
		String sql="update exam_subject set name=?,description=?,test_count=?,total_time=?,total_score=?,status=? where sub_id=?";
		return jdbcTemplate.update(sql,new Object[]{examSubject.getName()
				,examSubject.getDescription(),examSubject.getTestCount(),examSubject.getTotalTime()
				,examSubject.getTotalScore(),examSubject.getStatus(),examSubject.getSubId()});
	}

	
	//删除方法
	public Integer deleteExamSubject(Integer subId) throws Exception {
		String sql="delete from exam_subject where sub_id=?";
		return jdbcTemplate.update(sql,new Object[]{subId});
	}

	
	/**
	 * 
	 * 获取所有当前科目关联的学生在DAO中的查询代码
	 */
	//获取所有当前科目关联的学生在DAO中的查询代码
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllUserBySubject(Integer subjectId) {
		StringBuilder sql=new StringBuilder();
		sql.append(" select user.user_id as userId");
		sql.append(" from exam_user_subject eus");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id");
		sql.append(" left join exam_user user on user.user_id = eus.user_id");
		sql.append(" where es.sub_id = :subjectId");
		
		return this.createSqlQuery(String.valueOf(sql))
						.addScalar("userId",StringType.INSTANCE)
						.setInteger("subjectId", subjectId)
						.list();
	}
	
	//获取所有当前学生关联的科目在DAO中的查询代码
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllSubjectByUser(String userId) {
		StringBuilder sql=new StringBuilder();
		sql.append(" select es.sub_id as subId");
		sql.append(" from exam_user_subject eus");
		sql.append(" left join exam_user user on user.user_id = eus.user_id");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id");
		sql.append(" where user.user_id = :userId");
		
		return this.createSqlQuery(String.valueOf(sql))
						.addScalar("subId",StringType.INSTANCE)
						.setString("userId", userId)
						.list();
	}

//为科目新增学生
	@Override
	public void insertSubjectUsers(List<String> insertList, Integer subjectId) {
		String sql=null;
		//添加多条数据
		for(String userId:insertList){
			sql="insert into exam_user_subject(user_id,sub_id) values(:userId,:subjectId)";
			this.createSqlQuery(sql).setString("userId", userId).setInteger("subjectId", subjectId).executeUpdate();
		}
		
	}

	//为科目删除学生
	@Override
	public void deleteSubjectUsers(List<String> delList, Integer subjectId) {
		String sql=null;
		for(String userId:delList){
			sql="delete exam_user_subject from exam_user_subject where user_id=:userId and sub_id=:subjectId";
			this.createSqlQuery(sql).setString("userId", userId).setInteger("subjectId", subjectId).executeUpdate();
		}
		
	}

//使用模态框增加科目
	@Override
	public void addExamSubject(ExamSubject examSubject) {
		this.save(examSubject);
		
	}

//使用模态框修改科目
	@Override
	public void editExamTest(ExamSubject examSubject) {
		this.update(examSubject);		
	}

	//使用模态框删除科目
	@Override
	public void deleteSubject(Integer subId) {
		ExamSubject exsitsSubject=this.get(subId);
		if(exsitsSubject!=null){
			this.delete(exsitsSubject);
		}
		
	}


	//为该学生新增一个科目
	@Override
	public void insertUserSubjects(List<String> insertList, String userId) {
		String sql=null;
		//添加多条数据
		for(String subId:insertList){
			sql="insert into exam_user_subject(user_id,sub_id) values(:userId,:subId)";
			this.createSqlQuery(sql).setString("userId", userId).setString("subId", subId).executeUpdate();
		}
		
	}


	//为该学生删除一个科目
	@Override
	public void deleteUserSubjects(List<String> delList, String userId) {
		String sql=null;
		for(String subId:delList){
			sql="delete exam_user_subject from exam_user_subject where sub_id=:subId and user_id=:userId";
			this.createSqlQuery(sql).setString("subId", subId).setString("userId", userId).executeUpdate();
		}
		
	}


	//根据科目名称查询科目
	@Override
	public ExamSubject getSubjectByName(String subjectName) {
		String sql="select * from exam_subject where name=?";
		return jdbcTemplate.queryForObject(sql, new Object[]{subjectName},new RowMapper<ExamSubject>(){
			public ExamSubject mapRow(ResultSet rs,int num) throws SQLException{
				ExamSubject examSubject=new ExamSubject();
				examSubject.setSubId(rs.getInt("sub_id"));
				examSubject.setName(rs.getString("name"));
				examSubject.setDescription(rs.getString("description"));
				examSubject.setTestCount(rs.getInt("test_count"));
				examSubject.setTotalTime(rs.getInt("total_time"));
				examSubject.setTotalScore(rs.getDouble("total_score"));
				examSubject.setStatus(rs.getInt("status"));
				return examSubject;
			}
		});
	}


	


	
}
